#!/usr/bin/env bash

source /etc/profile

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
create TEMPORARY table mid_lube
select DISTINCT d.transaction_date minutes,d.station_id stationid,d.transaction_id transactionid,
d.item_value itemvalue,d.oillitre oillitre from
(
select A.transaction_id as transaction_id,C.transaction_date as transaction_date ,A.station_id as station_id,B.department_id as department_id,A.item_value as item_value ,trim(A.item_description)as oillitre
from realtime.transaction_items_notoil as A
join realtime.products as B
on A.product_code = B.product_code and B.station_id = 50006
join (SELECT transaction_date,transaction_id,station_id from realtime.transactions where substr(transaction_date,1,10)='$dt' ) as C
on C.transaction_id = A.transaction_id  and C.station_id=A.station_id
)d where d.department_id in (548,64);
select substr(minutes,1,16) minutes,stationID,count(DISTINCT transactionID) lubenumber,sum(substr(oillitre,-2,1)) lubelitre,sum(itemvalue) lubemoney
from mid_lube
group by substr(minutes,1,16),stationID;
EOF